#!/use/bin/python
# coding=utf-8
# 主动服务 开机率 analysis_startup_rate
# type,1已安装生活助手2已安装生活助手且订阅了服务3已安装生活助手但没有订阅服务  无需执行一次
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate  已安装生活助手
def insertStartupRateTyep1(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_startup_rate t where 1=1 and type = 1  and t.ref_date =  '{date}'")
        #获取 获取已安装生活助手的设备
        lifeAssistantNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from life_assistant_device t",1)
        #计算 当日开机设备量
        startupNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'deviceOn' and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}' ")
        # 计算开机率
        startupRate = getRateByXY(startupNum,lifeAssistantNum)

        if countNum == 0:
            logDetail = f"新增-{date}日安装生活助手的设备数量为{lifeAssistantNum},开机设备量为{startupNum},开机率为{startupRate}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_startup_rate(startup_rate,startup_num,push_num,ref_date,type) 
                value({startupRate},{startupNum},{lifeAssistantNum},'{date}',1)"""
            cur.execute(insertSql)
        else:
            logDetail = f"更新-{date}日安装生活助手的设备数量为{lifeAssistantNum},开机设备量为{startupNum},开机率为{startupRate}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_startup_rate set startup_rate = {startupRate} ,startup_num = {startupNum},push_num = {lifeAssistantNum} ,update_time = now()  where
                    ref_date = '{date}' and type = 1
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate  已安装生活助手且订阅了服务
def insertStartupRateTyep2(date,executeFlag):
    try:
        if executeFlag == 1:
            logStr = "执行一次"
            selectSql = f" from life_assistant_device t where (t.zjkj = 1 and t.zjkj_ui =1 and t.zjkj_execute_flag = 1) " \
                            f"or (t.jkzx = 1 and t.jkzx_ui =1 and t.jkzx_execute_flag = 1) or (t.yjyl = 1 and t.yjyl_ui =1 and t.yjyl_execute_flag = 1)" \
                            f"or (t.zjtx = 1 and t.jkzx_ui =1 and t.zjtx_execute_flag = 1)"
        else:
            logStr = "无需执行一次"
            selectSql = f" from life_assistant_device t where (t.zjkj = 1 and t.zjkj_ui =1) "\
                        f"or (t.jkzx = 1 and t.jkzx_ui =1 ) or (t.yjyl = 1 and t.yjyl_ui =1 ) or (t.zjtx = 1 and t.zjtx_ui = 1)"
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_startup_rate t where 1=1 and type = 2  and t.ref_date =  '{date}' and execute_flag = {executeFlag} ")
        #获取 已安装生活助手且订阅了服务(三个服务任一一个)
        subscribeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='all' and t.ref_date =  '{date}' and t.execute_flag = {executeFlag} ")
        if subscribeNum == 0:
            subscribeNum = selectNumBySql(f"select count(1) subNum {selectSql}")
        conn1, cur1 = getConfConnection()
        selectSql = f"""
                        select DISTINCT(t.active_id) activeId {selectSql}
                        """;
        cur1.execute(selectSql)
        selectResult = cur1.fetchall()
        #将订阅的激活id 拼接成过滤条件 如 and t.active_id in ('94467818','95154534','38697570')
        whereStr = "and t.active_id in ("
        for result in selectResult:
            activeId = result[0];
            whereStr = whereStr+"""'"""+activeId+"""',"""
        whereStr = whereStr[:-1] +")"
        #print(f"激活id过滤条件:{whereStr}")
        #计算 当日开机设备量
        startupNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'deviceOn' and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}' {whereStr}")
        # 计算开机率
        startupRate = getRateByXY(startupNum,subscribeNum)

        if countNum == 0:
            logDetail = f"{logStr}-新增-{date}日安装生活助手且订阅了服务的设备数量为{subscribeNum},开机设备量为{startupNum},开机率为{startupRate}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_startup_rate(startup_rate,startup_num,push_num,ref_date,type,execute_flag) 
                            value({startupRate},{startupNum},{subscribeNum},'{date}',2,{executeFlag})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{logStr}-更新-{date}日安装生活助手且订阅了服务的设备数量为{subscribeNum},开机设备量为{startupNum},开机率为{startupRate}"
            print(logDetail)
            # 更新
            updateSql = f"""
                            update analysis_startup_rate set startup_rate = {startupRate} ,startup_num = {startupNum},push_num = {subscribeNum} ,update_time = now()  where
                                ref_date = '{date}' and type = 2 and execute_flag = {executeFlag}
                        """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate  已安装生活助手但没有订阅服务 (无需执行一次)
def insertStartupRateTyep3(date,executeFlag):
    try:
        if executeFlag == 1:
            logStr = "执行一次"
        else:
            logStr = "无需执行一次"
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_startup_rate t where 1=1 and type = 3  and t.ref_date =  '{date}' and execute_flag = {executeFlag}")
        #获取 已安装生活助手但没有订阅服务的设备
        pushType1Num = selectNumBySql(
            f"select  t.push_num   from   analysis_startup_rate t where ref_date = '{date}' and t.type = 1 ")
        pushType2Num = selectNumBySql(
            f"select  t.push_num   from   analysis_startup_rate t where ref_date = '{date}' and t.type = 2 and execute_flag = {executeFlag}")
        #计算 当日开机设备量
        startupType1Num = selectNumBySql(
            f"select  t.startup_num   from   analysis_startup_rate t where ref_date = '{date}' and t.type = 1")
        startupType2Num = selectNumBySql(
            f"select  t.startup_num   from   analysis_startup_rate t where ref_date = '{date}' and t.type = 2 and execute_flag = {executeFlag}")
        # 计算开机率
        startupNum = startupType1Num-startupType2Num
        pushNum = pushType1Num-pushType2Num
        startupRate = getRateByXY(startupNum,pushNum)


        if countNum == 0:
            logDetail = f"{logStr}-新增-{date}日安装生活助手但没有订阅服务的设备数量为{pushNum},开机设备量为{startupNum},开机率为{startupRate}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_startup_rate(startup_rate,startup_num,push_num,ref_date,type,execute_flag) 
                                        value({startupRate},{startupNum},{pushNum},'{date}',3,{executeFlag})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{logStr}-更新-{date}日安装生活助手但没有订阅服务的设备数量为{pushNum},开机设备量为{startupNum},开机率为{startupRate}"
            print(logDetail)
            # 更新
            updateSql = f"""
                        update analysis_startup_rate set startup_rate = {startupRate} ,startup_num = {startupNum},push_num = {pushNum}
                         ,update_time = now()  where ref_date = '{date}' and type = 3 and execute_flag = {executeFlag}
                                    """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

#保存日志
def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 54
        jobName = '核心指标-开机率(安装生活助手)(无需执行一次)'
        tableName = 'analysis_startup_rate'
        # 插入日志表
        saveLogSql = f"""insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                                                value({jobId},'{jobName}','{detail}','{tableName}','{ref_date}')"""
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    yyMMddDate = getdateyyMMdd(d)
    print (f"{date} 日期,执行<核心指标-开机率(安装生活助手)>报表")
    insertStartupRateTyep1(date)
    insertStartupRateTyep2(date,0)
    insertStartupRateTyep3(date,0)
    insertStartupRateTyep2(date,1)
    insertStartupRateTyep3(date,1)


